import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import random

# 设置随机种子保证结果可复现
random.seed(42)
np.random.seed(42)
import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号
# 创建模拟数据
def generate_insurance_data(num_contracts=1000, start_date='2023-01-01', end_date='2024-12-31'):
    # 生成日期范围
    start = datetime.strptime(start_date, '%Y-%m-%d')
    end = datetime.strptime(end_date, '%Y-%m-%d')
    date_range = [start + timedelta(days=x) for x in range(0, (end - start).days + 1)]

    # 合同表
    contracts = []
    for i in range(1, num_contracts + 1):
        contract_date = random.choice(date_range)
        is_chief_reinsurer = random.choice([True, False])
        insurance_type = random.choice(['property', 'life'])
        policy_type = random.choice(['individual', 'group']) if insurance_type == 'life' else None
        payment_type = random.choice(['regular', 'lump']) if insurance_type == 'life' else None
        period = random.choice([1, 5, 10, 20]) if insurance_type == 'life' and payment_type == 'regular' else None
        estimated_premium = round(random.uniform(1000, 100000), 2)
        actual_premium = round(estimated_premium * random.uniform(0.8, 1.2), 2)
        is_canceled = random.choices([True, False], weights=[0.05, 0.95])[0]
        cancel_date = contract_date + timedelta(days=random.randint(1, 15)) if is_canceled else None

        contracts.append({
            'contract_id': i,
            'contract_date': contract_date,
            'is_chief_reinsurer': is_chief_reinsurer,
            'insurance_type': insurance_type,
            'policy_type': policy_type,
            'payment_type': payment_type,
            'period': period,
            'estimated_premium': estimated_premium,
            'actual_premium': actual_premium,
            'is_canceled': is_canceled,
            'cancel_date': cancel_date,
            'region': random.choice(['North', 'South', 'East', 'West'])
        })

    # 资产表（按季度）
    quarters = pd.date_range(start=start_date, end=end_date, freq='QE')
    assets = []
    total_assets = 10000000  # 初始资产
    for i, quarter in enumerate(quarters):
        premium_income = sum(c['actual_premium'] for c in contracts
                             if c['contract_date'] <= quarter and (not c['is_canceled'] or c['cancel_date'] > quarter))
        total_assets += premium_income * random.uniform(0.8, 1.2)
        assets.append({
            'quarter': quarter,
            'total_assets': round(total_assets, 2),
            'premium_income': round(premium_income, 2)
        })

    # 业务价值表
    business_values = []
    for contract in contracts:
        if contract['insurance_type'] == 'life' and not contract['is_canceled']:
            business_values.append({
                'contract_id': contract['contract_id'],
                'new_business_value': round(contract['actual_premium'] * random.uniform(0.5, 2.0), 2),
                'effective_business_value': round(contract['actual_premium'] * random.uniform(0.8, 3.0), 2)
            })

    return {
        'contracts': pd.DataFrame(contracts),
        'assets': pd.DataFrame(assets),
        'business_values': pd.DataFrame(business_values)
    }


# 生成数据
insurance_data = generate_insurance_data()


def calculate_metrics(data, report_date=None):
    if report_date is None:
        report_date = data['contracts']['contract_date'].max()

    contracts = data['contracts']
    assets = data['assets']
    business_values = data['business_values']

    # 筛选报告期数据
    report_contracts = contracts[contracts['contract_date'] <= report_date]
    active_contracts = report_contracts[(~report_contracts['is_canceled']) |
                                        (report_contracts['cancel_date'] > report_date)]

    # 指标1: 做首席再保人或非首席再保人的合同数量占比
    chief_count = active_contracts[active_contracts['is_chief_reinsurer']].shape[0]
    total_count = active_contracts.shape[0]
    chief_contract_ratio = chief_count / total_count * 100 if total_count > 0 else 0

    # 指标2: 做首席再保人或非首席再保人的保费收入占比
    chief_premium = active_contracts[active_contracts['is_chief_reinsurer']]['actual_premium'].sum()
    total_premium = active_contracts['actual_premium'].sum()
    chief_premium_ratio = chief_premium / total_premium * 100 if total_premium > 0 else 0

    # 指标3: 长/短期险保费增长率
    # 需要基期数据，这里假设基期为一年前
    base_date = report_date - timedelta(days=365)
    base_contracts = contracts[contracts['contract_date'] <= base_date]
    base_active = base_contracts[(~base_contracts['is_canceled']) |
                                 (base_contracts['cancel_date'] > base_date)]

    # 长期险(>=10年)和短期险
    long_term_current = active_contracts[(active_contracts['insurance_type'] == 'life') &
                                         (active_contracts['period'] >= 10)]['actual_premium'].sum()
    short_term_current = active_contracts[(active_contracts['insurance_type'] == 'life') &
                                          ((active_contracts['period'] < 10) |
                                           (active_contracts['period'].isna()))]['actual_premium'].sum()

    long_term_base = base_active[(base_active['insurance_type'] == 'life') &
                                 (base_active['period'] >= 10)]['actual_premium'].sum()
    short_term_base = base_active[(base_active['insurance_type'] == 'life') &
                                  ((base_active['period'] < 10) |
                                   (base_active['period'].isna()))]['actual_premium'].sum()

    long_term_growth = (long_term_current - long_term_base) / long_term_base * 100 if long_term_base > 0 else 0
    short_term_growth = (short_term_current - short_term_base) / short_term_base * 100 if short_term_base > 0 else 0

    # 指标4: 长/短期险保费占比
    total_life_premium = active_contracts[active_contracts['insurance_type'] == 'life']['actual_premium'].sum()
    long_term_ratio = long_term_current / total_life_premium * 100 if total_life_premium > 0 else 0
    short_term_ratio = short_term_current / total_life_premium * 100 if total_life_premium > 0 else 0

    # 指标5: 团/个险保费占比
    group_premium = active_contracts[(active_contracts['insurance_type'] == 'life') &
                                     (active_contracts['policy_type'] == 'group')]['actual_premium'].sum()
    individual_premium = active_contracts[(active_contracts['insurance_type'] == 'life') &
                                          (active_contracts['policy_type'] == 'individual')]['actual_premium'].sum()
    group_ratio = group_premium / total_life_premium * 100 if total_life_premium > 0 else 0
    individual_ratio = individual_premium / total_life_premium * 100 if total_life_premium > 0 else 0

    # 指标6: 首年期/趸缴保费占比
    new_first_year = active_contracts[(active_contracts['insurance_type'] == 'life') &
                                      (active_contracts['contract_date'] >= (report_date - timedelta(days=365)))]
    regular_new = new_first_year[new_first_year['payment_type'] == 'regular']['actual_premium'].sum()
    lump_new = new_first_year[new_first_year['payment_type'] == 'lump']['actual_premium'].sum()
    total_new = new_first_year['actual_premium'].sum()
    regular_ratio = regular_new / total_new * 100 if total_new > 0 else 0
    lump_ratio = lump_new / total_new * 100 if total_new > 0 else 0

    # 指标7: 10年期及以上期缴保费占比
    long_regular = new_first_year[(new_first_year['payment_type'] == 'regular') &
                                  (new_first_year['period'] >= 10)]['actual_premium'].sum()
    total_regular = new_first_year[new_first_year['payment_type'] == 'regular']['actual_premium'].sum()
    long_regular_ratio = long_regular / total_regular * 100 if total_regular > 0 else 0

    # 指标8: 犹豫期保费退保率
    canceled_new = new_first_year[new_first_year['is_canceled']]['actual_premium'].sum()
    new_premium = new_first_year['actual_premium'].sum()
    cancel_ratio = canceled_new / (new_premium + canceled_new) * 100 if (new_premium + canceled_new) > 0 else 0

    # 指标9: 新单业务价值占比
    new_business_value = business_values[business_values['contract_id'].isin(new_first_year['contract_id'])][
        'new_business_value'].sum()
    effective_business_value = business_values['effective_business_value'].sum()
    new_value_ratio = new_business_value / effective_business_value * 100 if effective_business_value > 0 else 0

    # 指标10: 资产增量保费比
    # 获取最近两个季度的资产数据
    recent_assets = assets[assets['quarter'] <= report_date].sort_values('quarter', ascending=False)
    if len(recent_assets) >= 2:
        current_assets = recent_assets.iloc[0]['total_assets']
        previous_assets = recent_assets.iloc[1]['total_assets']
        premium_income = recent_assets.iloc[0]['premium_income']
        asset_growth_ratio = (current_assets - previous_assets) / premium_income * 100 if premium_income > 0 else 0
    else:
        asset_growth_ratio = 0

    # 指标11: 保费预估差异率
    estimated_premium = active_contracts['estimated_premium'].sum()
    actual_premium = active_contracts['actual_premium'].sum()
    premium_diff_ratio = (estimated_premium - actual_premium) / actual_premium * 100 if actual_premium > 0 else 0

    # 返回所有指标
    metrics = {
        'chief_contract_ratio': round(chief_contract_ratio, 2),
        'chief_premium_ratio': round(chief_premium_ratio, 2),
        'long_term_growth': round(long_term_growth, 2),
        'short_term_growth': round(short_term_growth, 2),
        'long_term_ratio': round(long_term_ratio, 2),
        'short_term_ratio': round(short_term_ratio, 2),
        'group_ratio': round(group_ratio, 2),
        'individual_ratio': round(individual_ratio, 2),
        'regular_ratio': round(regular_ratio, 2),
        'lump_ratio': round(lump_ratio, 2),
        'long_regular_ratio': round(long_regular_ratio, 2),
        'cancel_ratio': round(cancel_ratio, 2),
        'new_value_ratio': round(new_value_ratio, 2),
        'asset_growth_ratio': round(asset_growth_ratio, 2),
        'premium_diff_ratio': round(premium_diff_ratio, 2),
        'report_date': report_date
    }

    return metrics


def calculate_metrics_by_period(data, period='month'):
    # 获取所有合同日期范围
    min_date = data['contracts']['contract_date'].min()
    max_date = data['contracts']['contract_date'].max()

    if period == 'day':
        dates = pd.date_range(start=min_date, end=max_date, freq='D')
    elif period == 'week':
        dates = pd.date_range(start=min_date, end=max_date, freq='W-MON')
    elif period == 'month':
        dates = pd.date_range(start=min_date, end=max_date, freq='ME')  # 使用 'ME' 代替 'M'
    elif period == 'year':
        dates = pd.date_range(start=min_date, end=max_date, freq='YE')  # 使用 'YE' 代替 'Y'
    else:
        raise ValueError("Invalid period. Choose from 'day', 'week', 'month', or 'year'")

    # 计算每个时间点的指标
    all_metrics = []
    for date in dates:
        metrics = calculate_metrics(data, date)
        all_metrics.append(metrics)

    # 转换为DataFrame
    metrics_df = pd.DataFrame(all_metrics)

    # 添加区域信息（如果需要按区域分析）
    # 这里简化处理，实际应用中可能需要按区域分组计算

    return metrics_df

# 计算不同时间粒度的指标
daily_metrics = calculate_metrics_by_period(insurance_data, 'day')
weekly_metrics = calculate_metrics_by_period(insurance_data, 'week')
monthly_metrics = calculate_metrics_by_period(insurance_data, 'month')
yearly_metrics = calculate_metrics_by_period(insurance_data, 'year')

# 导出到Excel
with pd.ExcelWriter('人寿保险业务看板.xlsx') as writer:
    daily_metrics.to_excel(writer, sheet_name='Daily Metrics', index=False)
    weekly_metrics.to_excel(writer, sheet_name='Weekly Metrics', index=False)
    monthly_metrics.to_excel(writer, sheet_name='Monthly Metrics', index=False)
    yearly_metrics.to_excel(writer, sheet_name='Yearly Metrics', index=False)

# 显示部分结果
print("Monthly Metrics Sample:")
print(monthly_metrics.head())

# 保存数据为CSV
monthly_metrics.to_csv('monthly_insurance_metrics.csv', index=False)


# 分析月度指标
# 分析月度指标
def analyze_metrics(metrics_df):
    analysis_results = {}

    # 计算各项指标的平均值
    analysis_results['average_metrics'] = metrics_df.mean(numeric_only=True)

    # 计算各项指标的趋势（最近3个月与之前3个月的比较）
    if len(metrics_df) >= 6:
        recent = metrics_df.iloc[-3:].mean(numeric_only=True)
        previous = metrics_df.iloc[-6:-3].mean(numeric_only=True)
        analysis_results['trend_comparison'] = (recent - previous) / previous * 100

    # 找出表现最好和最差的指标
    last_period = metrics_df.iloc[-1].drop('report_date')  # 排除 report_date 列
    analysis_results['best_metric'] = last_period.idxmax()
    analysis_results['worst_metric'] = last_period.idxmin()

    return analysis_results


# 分析月度指标
monthly_analysis = analyze_metrics(monthly_metrics)
print("\nMonthly Analysis Results:")
for key, value in monthly_analysis.items():
    print(f"\n{key}:")
    print(value)

import matplotlib.pyplot as plt

# 设置绘图风格
plt.style.use('ggplot')


# 创建几个关键指标的趋势图
def plot_key_metrics(metrics_df):
    fig, axes = plt.subplots(nrows=3, ncols=2, figsize=(15, 12))
    fig.suptitle('关键保险指标随时间的变化', fontsize=16)

    # 首席再保人保费占比
    metrics_df.plot(x='report_date', y='chief_premium_ratio', ax=axes[0, 0], title='首席再保人保费率（%）')

    # 长期险保费增长率
    metrics_df.plot(x='report_date', y='long_term_growth', ax=axes[0, 1], title='长期保费增长率（％）')

    # 团险占比
    metrics_df.plot(x='report_date', y='group_ratio', ax=axes[1, 0], title='团体保险比率（％）')

    # 10年期及以上期缴保费占比
    metrics_df.plot(x='report_date', y='long_regular_ratio', ax=axes[1, 1], title='10年以上定期支付比率（％）')

    # 新单业务价值占比
    metrics_df.plot(x='report_date', y='new_value_ratio', ax=axes[2, 0], title='新业务价值比率（%）')

    # 资产增量保费比
    metrics_df.plot(x='report_date', y='asset_growth_ratio', ax=axes[2, 1], title='资产增长至保费比率（%）')

    plt.tight_layout()
    plt.savefig('insurance_metrics_trends.png')
    plt.show()


# 绘制关键指标趋势
plot_key_metrics(monthly_metrics)


def calculate_metrics_by_region(data, report_date=None):
    if report_date is None:
        report_date = data['contracts']['contract_date'].max()

    contracts = data['contracts']
    active_contracts = contracts[(contracts['contract_date'] <= report_date) &
                                 ((~contracts['is_canceled']) | (contracts['cancel_date'] > report_date))]

    regions = active_contracts['region'].unique()
    region_metrics = []

    for region in regions:
        region_data = active_contracts[active_contracts['region'] == region]
        if len(region_data) == 0:
            continue

        # 计算部分关键指标
        chief_ratio = region_data[region_data['is_chief_reinsurer']].shape[0] / len(region_data) * 100
        chief_premium_ratio = region_data[region_data['is_chief_reinsurer']]['actual_premium'].sum() / \
                              region_data['actual_premium'].sum() * 100 if region_data[
                                                                               'actual_premium'].sum() > 0 else 0

        # 长期险占比（如果是人身险）
        life_data = region_data[region_data['insurance_type'] == 'life']
        long_term_ratio = life_data[life_data['period'] >= 10]['actual_premium'].sum() / \
                          life_data['actual_premium'].sum() * 100 if life_data['actual_premium'].sum() > 0 else 0

        region_metrics.append({
            'region': region,
            'contract_count': len(region_data),
            'premium_total': region_data['actual_premium'].sum(),
            'chief_contract_ratio': round(chief_ratio, 2),
            'chief_premium_ratio': round(chief_premium_ratio, 2),
            'long_term_ratio': round(long_term_ratio, 2),
            'report_date': report_date
        })

    return pd.DataFrame(region_metrics)


# 计算最新区域指标
latest_region_metrics = calculate_metrics_by_region(insurance_data)
print("\nLatest Region Metrics:")
print(latest_region_metrics)

# 导出区域指标
latest_region_metrics.to_csv('latest_region_metrics.csv', index=False)

if __name__ == "__main__":
    # 1. 生成模拟数据
    print("生成保险数据……")
    insurance_data = generate_insurance_data()

    # 2. 计算各项指标
    print("\n计算指标...")
    latest_metrics = calculate_metrics(insurance_data)
    print("\n最新指标:")
    for k, v in latest_metrics.items():
        print(f"{k}: {v}")

    # 3. 计算不同时间粒度的指标
    print("\n按时期计算指标...")
    monthly_metrics = calculate_metrics_by_period(insurance_data, 'month')

    # 4. 分析结果
    print("\n分析指标...")
    analysis_results = analyze_metrics(monthly_metrics)
    print("\n分析结果:")
    print(analysis_results)

    # 5. 导出结果
    print("\n导出结果...")
    with pd.ExcelWriter('人寿保险业务看板.xlsx') as writer:
        monthly_metrics.to_excel(writer, sheet_name='Monthly Metrics', index=False)
        calculate_metrics_by_region(insurance_data).to_excel(writer, sheet_name='Region Metrics', index=False)

    monthly_metrics.to_csv('monthly_insurance_metrics.csv', index=False)
    print("\n结果导出到 人寿保险业务看板.xlsx and monthly_insurance_metrics.csv")

    # 6. 可视化
    print("\n生成可视化...")
    plot_key_metrics(monthly_metrics)
    print("\n可视化保存到 insurance_metrics_trends.png")